﻿using Dapper;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Utility
{
    public class DapperDbContext<T> where T : BaseData
    {
        /// <summary>
        /// 获取实体类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected virtual T QueryEnt(int Id)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var sql = $"select * from [{tableName}] where Id=@Id";
            return conn.QueryFirstOrDefault<T>(sql, new { Id });
        }
        /// <summary>
        /// 获取实体类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected virtual IEnumerable<T> QueryList(int[] Ids)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var sql = $"select * from [{tableName}] where Id in @Ids";
            return conn.Query<T>(sql, new { Ids });
        }
        /// <summary>
        /// 获取数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected virtual int QueryCount(string where = "", object param = null)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var sql = $"select count(*) from [{tableName}]";
            if (!string.IsNullOrEmpty(where))
                sql += $" where {where}";
            return int.Parse(conn.ExecuteScalar(sql, param).ToString());
        }
        /// <summary>
        /// 查出一条记录的实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected virtual T QueryFirstOrDefault(string where = "", object param = null)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var sql = $"select * from [{tableName}]";
            if (!string.IsNullOrEmpty(where))
                sql += $" where {where}";
            return conn.QueryFirstOrDefault<T>(sql, param);
        }
        /// <summary>
        /// 查出多条记录的实体泛型集合
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <returns></returns>
        protected virtual IEnumerable<T> Query(string where = "", object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var sql = $"select * from [{tableName}]";
            if (!string.IsNullOrEmpty(where))
                sql += $" where {where}";
            return conn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
        }
        /// <summary>
        /// 自定义查询，返回单个字段
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        protected virtual dynamic ExecuteScalar(string column, string where, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var sql = $"select {column} from [{tableName}]";
            if (!string.IsNullOrEmpty(where))
                sql += $" where {where}";
            return conn.ExecuteScalar<dynamic>(sql, param, transaction, commandTimeout, commandType);
        }
        /// <summary>
        /// 自定义查询Count
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <returns></returns>
        protected virtual int QueryCustomCount(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            return conn.Query<int>(sql, param, transaction, buffered, commandTimeout, commandType).FirstOrDefault();
        }
        /// <summary>
        /// 自定义查询(返回泛型类)
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <returns></returns>
        protected virtual IEnumerable<T> QueryCustom(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            return conn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
        }
        /// <summary>
        /// 自定义查询(返回自定义类)
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <returns></returns>
        protected virtual IEnumerable<dynamic> QueryCustomDynamic(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            return conn.Query<dynamic>(sql, param, transaction, buffered, commandTimeout, commandType);
        }
        /// <summary>
        /// 获取存储过程
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <returns></returns>
        protected virtual IEnumerable<T> QueryProcedure(string procedureName, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            return conn.Query<T>(procedureName, param, transaction, buffered, commandTimeout, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 获取存储过程(返回自定义类)
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <returns></returns>
        protected virtual IEnumerable<dynamic> QueryProcedureDynamic(string procedureName, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            return conn.Query<dynamic>(procedureName, param, transaction, buffered, commandTimeout, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 同时查询多张表数据（高级查询）
        /// "select *from K_City;select *from K_Area";
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected virtual Dictionary<int, List<dynamic>> QueryMultiple(string sql, object param = null, int readCount = 1, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            Dictionary<int, List<dynamic>> resultList = new Dictionary<int, List<dynamic>>();
            using (IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection())
            {
                using (var multi = conn.QueryMultiple(sql, param, transaction, commandTimeout, commandType))
                {
                    if (!multi.IsConsumed)//没释放
                    {
                        for (int i = 0; i < readCount; i++)
                            resultList.Add(i, multi.Read().ToList());
                    }
                }
            }
            return resultList;
        }
        /// <summary>
        /// 添加单实体
        /// </summary>
        /// <returns></returns>
        protected virtual int Add(T model)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string pkName = CommonHelper.GetKeyField<T>().ToString();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var ps = model.GetType().GetProperties();
            List<string> @colms = new List<string>();
            List<string> @params = new List<string>();
            foreach (var p in ps)
            {
                if (p.Name != pkName && p.CustomAttributes.All(x => x.AttributeType != typeof(NotMappedAttribute)))
                {
                    @colms.Add(string.Format("[{0}]", p.Name));
                    @params.Add(string.Format("@{0}", p.Name));
                }
            }
            var sql = $"INSERT INTO [{tableName}] ({string.Join(", ", @colms)}) VALUES({string.Join(", ", @params)}) select @@identity";
            return conn.ExecuteScalar<int>(sql, model);
        }
        /// <summary>
        /// 批量添加
        /// </summary>
        /// <returns></returns>
        protected virtual int AddBatch(List<T> list)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string pkName = CommonHelper.GetKeyField<T>().ToString();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var ps = list.FirstOrDefault().GetType().GetProperties();
            List<string> @colms = new List<string>();
            List<string> @params = new List<string>();
            foreach (var p in ps)
            {
                if (p.Name != pkName && p.CustomAttributes.All(x => x.AttributeType != typeof(NotMappedAttribute)))
                {
                    @colms.Add(string.Format("[{0}]", p.Name));
                    @params.Add(string.Format("@{0}", p.Name));
                }
            }
            var sql = $"INSERT INTO [{tableName}] ({string.Join(", ", @colms)}) VALUES({string.Join(", ", @params)})";
            return conn.Execute(sql, list);
        }
        /// <summary>
        /// 修改单实体
        /// </summary>
        /// <returns></returns>
        protected virtual int Edit(T model)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string pkName = CommonHelper.GetKeyField<T>().ToString();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var ps = model.GetType().GetProperties();
            string where = "";
            string update = "";
            foreach (var p in ps)
            {
                if (pkName == p.Name)
                    where += $" {p.Name} = @{p.Name} and";
                if (p.CustomAttributes.All(x => x.AttributeType != typeof(NotMappedAttribute) && x.AttributeType != typeof(EditNoMappedAttribute)) && p.Name != pkName && p.GetValue(model) != null)
                    update += $"{p.Name} = @{p.Name} ,";
            }
            where = where.TrimEnd(new char[] { 'd', 'n', 'a' });
            update = update.TrimEnd(',');
            var sql = $"update { tableName} set {update} where {where}";
            return conn.Execute(sql, model);
        }
        /// <summary>
        /// 批量修改
        /// </summary>
        /// <returns></returns>
        protected virtual int EditBatch(List<T> list)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string pkName = CommonHelper.GetKeyField<T>().ToString();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var ent = list.FirstOrDefault();
            var ps = ent.GetType().GetProperties();
            string update = "";
            foreach (var p in ps)
            {
                if (p.CustomAttributes.All(x => x.AttributeType != typeof(NotMappedAttribute) && x.AttributeType != typeof(EditNoMappedAttribute)) && p.Name != pkName && p.GetValue(ent) != null)
                    update += $"{p.Name} = @{p.Name} ,";
            }
            update = update.TrimEnd(',');
            var sql = $"update { tableName} set {update} where {pkName} =@{pkName}";
            return conn.Execute(sql, list);
        }
        /// <summary>
        /// 批量修改
        /// </summary>
        /// <returns></returns>
        protected virtual int EditBatch(T ent, int[] Ids)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string pkName = CommonHelper.GetKeyField<T>().ToString();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var ps = ent.GetType().GetProperties();
            string update = "";
            foreach (var p in ps)
            {
                if (p.CustomAttributes.All(x => x.AttributeType != typeof(NotMappedAttribute) && x.AttributeType != typeof(EditNoMappedAttribute)) && p.Name != pkName && p.GetValue(ent) != null)
                    update += $"{p.Name} = @{p.Name} ,";
            }
            update = update.TrimEnd(',');
            var sql = $"update { tableName} set {update} where {pkName} in ({string.Join(",", Ids)})";
            return conn.Execute(sql, ent);
        }
        /// <summary>
        /// 删除(物理删除)
        /// </summary>
        /// <returns></returns>
        protected virtual int Del(T ent)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string pkName = CommonHelper.GetKeyField<T>().ToString();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var sql = $"delete { tableName} where {pkName} =@{pkName}";
            return conn.Execute(sql, ent);
        }
        /// <summary>
        /// 批量删除(物理删除)
        /// </summary>
        /// <returns></returns>
        protected virtual int DelBatch(int[] Ids)
        {
            using IDbConnection conn = DapperHelper.Instance.OpenCurrentDbConnection();
            string pkName = CommonHelper.GetKeyField<T>().ToString();
            string tableName = CommonHelper.GetTableField<T>().ToString();
            var sql = $"delete { tableName} where {pkName} in ({string.Join(",", Ids)})";
            return conn.Execute(sql);
        }
          

    }
}
